package cn.tool.example;


import cn.tool.dao.excel.read.FujinRead;
import cn.tool.dao.excel.write.FujinWrite;
import cn.tool.dao.mapper.AreaInfoMapper;
import cn.tool.dao.mapper.JjzzMapper;
import cn.tool.dao.mapper.NewOrgMapper;
import cn.tool.dao.pojo.AreaInfo;
import cn.tool.dao.pojo.Jjzz;
import cn.tool.dao.pojo.NewOrg;
import cn.tool.dao.pojo.excelpojo.ExcelDepartment;
import cn.tool.dao.pojo.excelpojo.ExcelOrg;
import cn.tool.dao.pojo.excelpojo.ExcelUser;
import cn.tool.utils.StringUtils;
import cn.tool.utils.TransferFujinDataUtils;
import com.alibaba.fastjson.JSON;
import org.apache.commons.lang3.ObjectUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;

import java.util.*;
import java.util.stream.Collectors;

@Component
public class TransferFujinData {

    /**
     * 修改这里的参数 和 yml里的数据库参数 ，再 test里面执行这个程序即可；
     */
    //要查询的镇的行政区划 9位
    private final static String ORIGIN_QUERY_AREA_CODE = "230882106";
    //要查询的镇的新的行政区划
    private final static String TARGET_QUERY_AREA_CODE = "230882106";

    private final static String TARGERT_TOWN_NAME = "砚山镇";
    //源数据库名称
    private final static String ORIGIN_DATABASE = "sz_fujin_changan";
    //新建数据库名称
    private final static String TARGET_DATABASE = "yanshanzhen";

    //数据库连接
    private String localdataSource = "jdbc:mysql:///sz_fujin_changan?useUnicode=true&characterEncoding=utf-8&serverTimeZone=Asia/Shanghai";


    /**
     * 以下不能动
     */
    private final static String DATABASE_NAME = "origin_database";
    private final static String NEW_DATABASE_NAME = "target_database";
    private final static String NEW_AREA_CODE = "replace_area_code";


    private String addInfoTablePath = "src/main/resources/file/fujinsql/add_area_info_table.sql";

    private String addColumeForJjzzPath = "src/main/resources/file/fujinsql/add_jjzz_column.sql";


    private String createDatabasePath = "src/main/resources/file/fujinsql/targert_database_template.sql";
    private String executeCreateDatabasePath = "src/main/resources/file/fujinsql/executesql/targert_database_template.sql";

    private String transferSqlPath = "src/main/resources/file/fujinsql/fujin_data_transfer.sql";
    private String executeTransferSqlPath = "src/main/resources/file/fujinsql/executesql/fujin_data_transfer.sql";


    @Autowired
    private JjzzMapper jjzzMapper;

    @Autowired
    private AreaInfoMapper areaInfoMapper;

    @Autowired
    private NewOrgMapper newOrgMapper;


    public void transfer() throws Exception {
        //本地添加行政区划表
        TransferFujinDataUtils.executeForDatabase(localdataSource, addInfoTablePath);

        //jjzz表中添加修改后镇的行政区划
        TransferFujinDataUtils.executeForDatabase(localdataSource, addColumeForJjzzPath);


        List<Jjzz> jjzzes = jjzzMapper.selectByAreaCode(ORIGIN_QUERY_AREA_CODE);
        Map<String, Jjzz> jjzzCodeMap = jjzzes.stream().collect(Collectors.toMap(Jjzz::getCode, item -> item));

        List<AreaInfo> areaInfos = areaInfoMapper.selectByAreaCode(TARGET_QUERY_AREA_CODE + "000");
        Map<String, AreaInfo> areaInfosMap = areaInfos.stream().collect(Collectors.toMap(AreaInfo::getAreaName, item -> item));

        LinkedList<Jjzz> NonDefinedJJzzes = new LinkedList<>();

        //存放经济组织赋值信息；
        Map<String, String> orgCodeMap = new HashMap<>();

        jjzzes.forEach(item -> {
            //针对命名不规范做出调整
            String key = "";
            if (item.getName().contains("村")) {
                key = item.getName() + "委会";
            } else if (!item.getName().contains("村") && !item.getName().contains("股份经济合作社")) {
                key = item.getName() + "村委会";
            }

            AreaInfo areaInfo = areaInfosMap.get(key);
            if (ObjectUtils.isNotEmpty(areaInfo)) {
                item.setAreaCode(areaInfo.getAreaCode());
                item.setOrgCode(areaInfo.getAreaCode() + "000");
                orgCodeMap.put(areaInfo.getAreaCode(), areaInfo.getAreaCode() + "000");
                jjzzMapper.updateById(item);
            } else {
                NonDefinedJJzzes.add(item);
            }
        });

        //移除股份经济合作社
        LinkedList<Jjzz> removeList = new LinkedList<>();
        for (int i = 0; i < NonDefinedJJzzes.size(); i++) {
            Jjzz item = NonDefinedJJzzes.get(i);
            String key = item.getName();

            if (StringUtils.contains(key, "股份经济合作社")) {
                Jjzz jjzz = jjzzCodeMap.get(item.getAreaCode() + "000");

                if (ObjectUtils.isNotEmpty(jjzz) && ObjectUtils.isNotEmpty(jjzz.getAreaCode())) {

                    String s = getOrgCodeAddOne(orgCodeMap, orgCodeMap.get(jjzz.getAreaCode()));
                    item.setAreaCode(jjzz.getAreaCode());
                    item.setOrgCode(s);
                    jjzzMapper.updateById(item);
                    removeList.addLast(item);
                }

            }

        }
        while (removeList.size() > 0) {
            NonDefinedJJzzes.remove(removeList.removeLast());
        }

        //将剩下的村子添加到其他的村子下面
        for (int i = 0; i < NonDefinedJJzzes.size(); i++) {
            Jjzz item = NonDefinedJJzzes.get(i);

            //先去表里面查看对应村子的行政区划代码，然后再去orgcodemap里面取最新的经济组织编码；
            List<NewOrg> newOrgs = newOrgMapper.selectNewOrgByAreaAndName(TARGET_QUERY_AREA_CODE, item.getName());
            if (newOrgs.size() > 1) {
                int j = 1;
                for (NewOrg newOrg : newOrgs) {
                    System.out.println("新经济组织表中查询到多条相同数据:" + j++ + ".----" + JSON.toJSON(newOrg));
                }
            } else if (newOrgs.size() == 1) {
                item.setAreaCode(newOrgs.get(0).getAreaCode());
                item.setOrgCode(getOrgCodeAddOne(orgCodeMap, newOrgs.get(0).getAreaCode()));
                jjzzMapper.updateById(item);
//                NonDefinedJJzzes.remove(item);
                removeList.addLast(item);
            } else {
                System.out.println("该经济组织未找到匹配的 新经济组织数据" + item.getCode() + "-----" + item.getName());
            }
        }

        while (removeList.size() > 0) {
            NonDefinedJJzzes.remove(removeList.removeLast());
        }


        //此步骤为手动输入未定义的经济组织代码
        Scanner input = new Scanner(System.in);
        System.out.println("共有" + NonDefinedJJzzes.size() + "个经济组织未定义成功,需要手动操作. 分别为");
        NonDefinedJJzzes.forEach(item -> {
            System.out.println("经济组织为" + item.getCode() + "---行政区划为" + item.getPcode() + "---名称为" + item.getName());
        });
        for (Jjzz item : NonDefinedJJzzes) {
            String orgcode = "";
            boolean flag = true;
            while (orgcode.length() != 15 || flag) {
                System.out.println("请输入---" + item.getName() + "---" + item.getCode() + "---的经济组织代码");
                orgcode = input.next();
                System.out.println("是否确认 输入Y or N");
                String next = input.next();
                if (next.equals("Y") || next.equals("y")) {
                    flag = false;
                }
            }
            item.setOrgCode(orgcode);
            item.setAreaCode(orgcode.substring(0, 12));
            jjzzMapper.updateById(item);
        }

        //修改sql语句,并执行创建数据库和转换脚本
        TransferFujinDataUtils.replaceStringForFile(createDatabasePath, NEW_DATABASE_NAME, TARGET_DATABASE, executeCreateDatabasePath);
        TransferFujinDataUtils.executeForDatabase(localdataSource, executeCreateDatabasePath);
        Thread.sleep(10000);
        String transfer = TransferFujinDataUtils.replaceStringForFile(transferSqlPath, NEW_DATABASE_NAME, TARGET_DATABASE);
        transfer = org.apache.commons.lang3.StringUtils.replace(transfer, DATABASE_NAME, ORIGIN_DATABASE);
        transfer = org.apache.commons.lang3.StringUtils.replace(transfer, NEW_AREA_CODE, TARGET_QUERY_AREA_CODE);
        TransferFujinDataUtils.outputToFile(transfer, executeTransferSqlPath);
        TransferFujinDataUtils.executeForDatabase(localdataSource, executeTransferSqlPath);


        //生成经济组织表格,和用户表等;
        List<Jjzz> jjzzList = jjzzMapper.selectByNewAreaCode(TARGET_QUERY_AREA_CODE);
        List<NewOrg> newOrgList = newOrgMapper.selectByAreaCode(TARGET_QUERY_AREA_CODE);
        Map<String, List<NewOrg>> newOrgMap = newOrgList.stream().collect(Collectors.groupingBy(item -> item.getOrgCode().substring(0, 14)));

        List<ExcelOrg> excelOrgs = new ArrayList<>();
        List<Jjzz> notExcelOrgs = new ArrayList<>();

        List<ExcelUser> excelUsers = new ArrayList<>();

        List<ExcelDepartment> excelDepartments = new ArrayList<>();

        HashMap<String, String> updateUsersMap = new HashMap<>();

        //根据jjzz的个数生成对象经济组织对象;
        jjzzList.forEach(item -> {
            String name = item.getName();
            String key = item.getOrgCode().substring(0, 14);

            List<NewOrg> otem = newOrgMap.get(key);
            if (ObjectUtils.isNotEmpty(otem)) {
                if (otem.size() > 1) {
                    Map<String, List<NewOrg>> nameMap = otem.stream().collect(Collectors.groupingBy(NewOrg::getName));

                    List<NewOrg> orgs = nameMap.get(name);

                    if (ObjectUtils.isNotEmpty(orgs)) {

                        updateUsersMap.put(otem.get(0).getOrgCode(), item.getOrgCode());
                        ExcelOrg res = ExcelOrg.creat(item, otem.get(0));
                        excelOrgs.add(res);

                    } else if (ObjectUtils.isEmpty(orgs)) {
                        boolean flag = true;
                        for (NewOrg org : otem) {
                            if (StringUtils.contains(org.getName(), name)) {
                                updateUsersMap.put(org.getOrgCode(), item.getOrgCode());
                                ExcelOrg res = ExcelOrg.creat(item, org);
                                excelOrgs.add(res);
                                flag = false;
                                break;
                            }
                        }
                        if (flag) {
                            notExcelOrgs.add(item);
                        }

                    } else {
                        notExcelOrgs.add(item);
                    }

                } else if (otem.size() == 1) {
                    updateUsersMap.put(otem.get(0).getOrgCode(), item.getOrgCode());
                    ExcelOrg res = ExcelOrg.creat(item, otem.get(0));
                    excelOrgs.add(res);
                } else {
                    notExcelOrgs.add(item);
                }
            } else {
                notExcelOrgs.add(item);
            }
        });

        //根据用户表进行用户表的导出
        List<ExcelUser> users = FujinRead.getExcelUserList().stream().filter(i -> StringUtils.contains(i.getAreaCode(), TARGET_QUERY_AREA_CODE)).collect(Collectors.toList());

        users.forEach(item -> {
            String key = item.getOrgCode();
            if (StringUtils.contains(key, "0")) {

                String newOrgcode = updateUsersMap.get(key);

                if (ObjectUtils.isNotEmpty(newOrgcode)) {
                    item.setOrgCode(newOrgcode);
                    excelUsers.add(item);
                } else {
                    excelUsers.add(item);
                }

            } else {
                ExcelDepartment excelDepartment = new ExcelDepartment();
                excelDepartment.setAreaCode(item.getAreaCode());
                excelDepartment.setDepartmentName(item.getOrgCode());
                excelDepartments.add(excelDepartment);
                excelUsers.add(item);
            }
        });


        FujinWrite fujinWrite = new FujinWrite(TARGERT_TOWN_NAME);

        //经济组织表格优化参数
        excelOrgs.forEach(item -> {
            String orgCode = item.getOrgCode();

            if (orgCode.endsWith("000")) {

                item.setLevel("本级");
                String name = item.getName();
                if (StringUtils.contains(name, "股份经济合作社")) {
                    item.setName(name.substring(name.indexOf("镇") + 1, name.indexOf("股")));
                }

            } else {

                item.setLevel("其他");
                String name = item.getName();
                if (!StringUtils.contains(name, "股份经济合作社")) {
                    item.setName(item.getAddr() + "股份经济合作社");
                }

            }
        });

        fujinWrite.exportOrg(excelOrgs);
        fujinWrite.exportUser(excelUsers);

        //部门表去重
        List<ExcelDepartment> collect = excelDepartments.stream().distinct().collect(Collectors.toList());
        fujinWrite.exportDepartment(collect);

        if (ObjectUtils.isNotEmpty(notExcelOrgs)) {
            System.out.println("--------------以下经济组织未成功导出未excel表格---------------");
            for (Jjzz notExcelOrg : notExcelOrgs) {
                System.out.println(JSON.toJSON(notExcelOrg));
            }
        }


        input.close();

    }

    private String getOrgCodeAddOne(Map<String, String> orgCodeMap, String key) {
        if (ObjectUtils.isNotEmpty(orgCodeMap.get(key))) {
            String s = orgCodeMap.get(key);
            s = s.substring(0, 14) + (Integer.valueOf(s.substring(13)) + 1);
            orgCodeMap.remove(key);
            orgCodeMap.put(key, s);
            return s;
        } else {
            orgCodeMap.put(key, key + "000");
            return key + "000";
        }
    }


}
